Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Web.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions
Namespace WorkFlow.DAO
    Public Class CodeMappingDAO
        Inherits OAMS.DAO.BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub
        Public Function GetData(ByVal TableName As String, ByVal ColumnName As String) As DataTable

            Dim sql As New StringBuilder


            sql.AppendFormat("SELECT a.*,b.Text as TableName_Text,c.Text as ColumnName_Text,CASE when a.Active='Y' then '1' else '0' end as IsActive FROM [CodeMapping] a left join CodeList b on a.TableName=b.TableName and b.TableName=b.ColumnName left join CodeList c on a.TableName=c.TableName and a.ColumnName=c.ColumnName and c.TableName<>c.ColumnName  ")
            sql.AppendFormat("WHERE ((a.TableName = '{0}' or '{0}' ='0') AND (a.ColumnName = '{1}'  or '{1}'='0'))   ", TableName, ColumnName)
            SQL.AppendFormat("and b.Active='Y' and c.Active='Y' ")
            sql.AppendFormat("ORDER BY a.TableName,a.ColumnName,Sequence ")


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sql.ToString()).Tables(0)

        End Function


        Public Sub InsertData(ByVal TableName As String, ByVal ColumnName As String, ByVal Value As String, ByVal Text As String, ByVal Active As String, ByVal ChnText As String, ByVal EngText As String, ByVal UID As String)

            Dim sqlstr As New StringBuilder


            sqlstr.AppendFormat(" insert into codemapping(TableName,ColumnName,Value,Text,ChnText,EngText,Sequence,Active,CreateDate,CreateBy) ")
            sqlstr.AppendFormat(" select ")
            sqlstr.AppendFormat(" '{0}', ", TableName)
            sqlstr.AppendFormat(" '{0}', ", ColumnName)
            sqlstr.AppendFormat(" '{0}', ", Value)
            sqlstr.AppendFormat(" N'{0}', ", Text)
            sqlstr.AppendFormat(" N'{0}', ", ChnText)
            sqlstr.AppendFormat(" N'{0}', ", EngText)
            sqlstr.AppendFormat(" (select max(sequence)+1 from codemapping ")
            sqlstr.AppendFormat(" where TableName='{0}' ", TableName)
            sqlstr.AppendFormat(" and ColumnName='{0}'), ", ColumnName)
            sqlstr.AppendFormat(" '{0}', ", Active)
            sqlstr.AppendFormat(" getdate(), ")
            sqlstr.AppendFormat(" '{0}' ", UID)

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sqlstr.ToString())
        End Sub

        Public Sub UpdateData(ByVal Text As String, ByVal ChnText As String, ByVal EngText As String, ByVal Active As String, ByVal ID As String)

            Dim sqlstr As New StringBuilder


            sqlstr.AppendFormat(" update codemapping ")
            sqlstr.AppendFormat(" set Text = N'{0}' ", Text)

            sqlstr.AppendFormat(",ChnText=N'{0}' ", ChnText)
            sqlstr.AppendFormat(",EngText=N'{0}' ", EngText)

            sqlstr.AppendFormat(",Active='{0}' ", Active)
            sqlstr.AppendFormat(" where ")
            sqlstr.AppendFormat(" ID ='{0}' ", ID)


            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sqlstr.ToString())

        End Sub

        Public Function GetddlTableNameItem() As DataTable
            Dim sql As New StringBuilder
            sql.AppendFormat("Select * from CodeList where TableName=ColumnName and Active='Y' ")
        
            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sql.ToString()).Tables(0)

        End Function
        Public Function GetddlColumnNameItem(ByVal TableName As String) As DataTable
            Dim sql As New StringBuilder


            sql.AppendFormat("select b.* from CodeList a left join CodeList b on a.TableName=b.TableName and b.TableName<>b.ColumnName where a.TableName=a.ColumnName and a.TableName='{0}' and a.Active='Y' and b.Active='Y' ", TableName)

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sql.ToString()).Tables(0)

        End Function
        Public Function CodeMappingIsExist(ByVal TableName As String, ByVal ColumnName As String, ByVal Value As String) As Boolean
            Dim sql As New StringBuilder
            sql.AppendFormat("Select * From CodeMapping ")
            sql.AppendFormat("where TableName='{0}' ", TableName)
            sql.AppendFormat("and ColumnName='{0}' ", ColumnName)
            sql.AppendFormat("and Value='{0}' ", Value)

            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sql.ToString()).Tables(0).Rows.Count > 0

        End Function
        Public Function CodeMappingIDIsExist(ByVal TableName As String, ByVal ColumnName As String, ByVal Value As String, ByVal ID As String) As Boolean
            Dim sql As New StringBuilder

            sql.AppendFormat("Select * From CodeMapping ")
            sql.AppendFormat("where TableName='{0}' ", TableName)
            sql.AppendFormat("and ColumnName='{0}' ", ColumnName)
            sql.AppendFormat("and Value='{0}' ", Value)
            sql.AppendFormat("and ID<>'{0}' ", ID)


            Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sql.ToString()).Tables(0).Rows.Count > 0

        End Function
        Public Sub DeleteData(ByVal ID As String)
            Dim sql As New StringBuilder
            sql.AppendFormat("DELETE FROM [CodeMapping] WHERE [ID] = '{0}' ", ID)

            SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sql.ToString())

        End Sub
    
    End Class
End Namespace